import logging, os, pymysql
import re
from itertools import chain

from tests.dayu_business_background.game import activityapi as ac
from common.config import *


class OperationDb():
    # 环境，数据库表名，用户名，密码都是变量
    def __init__(self, env):
        host_db = getconf(env, "host_db")
        user_db = getconf(env, "user_db")
        password = getconf(env, "password")
        port = getconf(env, "port")
        try:
            self.conn = pymysql.connect(host=host_db, user=user_db, password=password, port=int(port), charset="utf8")
            # self.conn=pymysql.connect(host=host_db, user=user_db,password=password,database="platform_game",charset="utf8")
            self.cur = self.conn.cursor(pymysql.cursors.DictCursor)
            # self.cur = self.conn.cursor()
            # print("数据库连接成功")
        except pymysql.Error as e:
            print("数据库连接失败|Mysql Error %d:%s" % (e.args[0], e.args[1]))

    # 单条数据操作,查询
    def get_sql(self, conndition):
        self.cur.execute(conndition)
        data = self.cur.fetchall()
        self.conn.commit()
        return data

    # 单条数据操作，包括删除，更新
    def op_sql(self, conndition):
        try:
            self.cur.execute(conndition)
            self.conn.commit()
            # print ("执行了吗")
            result = {"code": "0000", "messqge": "执行通用操作成功", "data": []}
        except pymysql.Error as e:
            print(e)
            self.conn.rollback()
            result = {"code": "9999", "messqge": "执行通用操作异常", "data": []}
            # print("操作数据库失败|Mysql Error %d:%s" % (e.args[0], e.args[1]))
        print(result)
        return result

    # 查询表中单条数据
    def select_one(self, conndition):
        try:
            rows_affect = self.cur.execute(conndition)
            if rows_affect > 0:
                results = self.cur.fetchone()
                result = {"code": "0000", "messqge": "执行单条查询成功", "data": results}
            else:
                result = {"code": "0000", "messqge": "执行单条查询成功", "data": []}
        except pymysql.Error as e:
            self.conn.rollback()
            result = {"code": "9999", "messqge": "执行单条查询异常", "data": []}
            print("查询单条数据失败|Mysql Error %d:%s" % (e.args[0], e.args[1]))
        # 处理返回值，去掉逗号，单括号和单引号
        select = re.findall(r'\d+', str(result["data"]))
        selectone = str(select).replace('[', '').replace(']', '').replace("'", "")
        return selectone

    # 查询表中多条数据
    def select_all(self, conndition):
        try:
            rows_affect = self.cur.execute(conndition)
            if rows_affect > 0:
                self.cur.scroll(0, mode='absolute')  # 鼠标光标回到初始位置
                results = self.cur.fetchall()
                result = {"code": "0000", "messqge": "执行批量查询成功", "data": results}
            else:
                result = {"code": "0000", "messqge": "执行批量查询成功", "data": []}
        except pymysql.Error as e:
            self.conn.rollback()
            result = {"code": "9999", "messqge": "执行批量查询异常", "data": []}
            print("查询多条数据失败|Mysql Error %d:%s" % (e.args[0], e.args[1]))
        select = result["data"]
        selectall = str(select).replace('(', '').replace(')', '').replace("',", '').replace("'", "").replace(" ", "")
        return selectall

    # 插入数据
    def insert_data(self, conndition, params):
        try:
            results = self.cur.executemany(conndition, params)  # 返回插入的数据条数
            self.conn.commit()
            result = {"code": "0000", "messqge": "执行批量插入成功", "data": results}
        except pymysql.Error as e:
            self.conn.rollback()
            result = {"code": "9999", "messqge": "执行批量插入异常", "data": []}
            print("插入数据失败|Mysql Error %d:%s" % (e.args[0], e.args[1]))
        return result

    # 插入数据
    def insert_single_data(self, conndition):
        try:
            results = self.cur.execute(conndition)  # 返回插入的数据条数
            self.conn.commit()
            result = {"code": "0000", "messqge": "执行插入成功", "data": results}
        except pymysql.Error as e:
            self.conn.rollback()
            result = {"code": "9999", "messqge": "执行插入异常", "data": []}
            print("插入数据失败|Mysql Error %d:%s" % (e.args[0], e.args[1]))
        return result

    # 数据库关闭
    def __del_(self):
        if self.cur != None:
            self.cur.close()
        if self.conn != None:
            self.conn.close()


if __name__ == "__main__":
    result = OperationDb("test").get_sql("select left_stock from platform_game.activity_prize where activity_id = 7271 LIMIT 1;")
    print(result[0]["left_stock"])
